using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace BooksManagermentSystem
{
    internal class DBhelper
    {
        private static SqlConnection conn  = new SqlConnection("server=.;database=BooksManagermentSystem;uid=sa;pwd=******");
        private static SqlCommand cmd;
        private static SqlDataAdapter adapter;

        /// <summary>
        /// 验证学生账号
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static bool checkStu(string name,string pwd)
        {
            string sql = $"select * from StuInfo where stuid='{@name}' and stupwd='{@pwd}'";
            try
            {
                //防止sql注入
                if(sql.IndexOf(" or") != -1) { throw new Exception("草泥马，想搞事？"); }
                conn.Open();
                cmd = new SqlCommand(sql,conn);
                object obj = cmd.ExecuteScalar();
                if(obj != null)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally { conn.Close(); }
        }

        /// <summary>
        /// 验证学生是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool checkStu(string id)
        {
            string sql = $"select * from StuInfo where stuid = '{id}'";
            conn.Open() ;
            cmd = new SqlCommand(sql,conn);
            object obj = cmd.ExecuteScalar();
            if(obj != null)
            {
                conn.Close();
                return true;
            }
            conn.Close();
            return false;
        }

        public static bool checkAdmin(string id)
        {
            string sql = $"select * from AdminInfo where account = '{id}'";
            conn.Open();
            cmd = new SqlCommand(sql, conn);
            object obj = cmd.ExecuteScalar();
            if (obj != null)
            {
                conn.Close();
                return true;
            }
            conn.Close();
            return false;
        }

        /// <summary>
        /// 验证书籍是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool checkBook(string id)
        {
            string sql = $"select * from BookInfo where bookid = '{id}'";
            conn.Open();
            cmd = new SqlCommand(sql, conn);
            object obj = cmd.ExecuteScalar();
            if (obj != null)
            {
                conn.Close();
                return true;
            }
            conn.Close();
            return false;
        }

        /// <summary>
        /// 验证书籍类型是否存在
        /// </summary>
        /// <param name="booktypename"></param>
        /// <returns></returns>
        public static bool checkBooktype(string booktypename)
        {
            string sql = $"select * from BookType where typename = '{booktypename}'";
            conn.Open();
            cmd = new SqlCommand(sql, conn);
            object obj = cmd.ExecuteScalar();
            if (obj != null)
            {
                conn.Close();
                return true;
            }
            conn.Close();
            return false;
        }

        /// <summary>
        /// 验证管理员账号
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static bool checkAdmin(string name,string pwd)
        {
            string sql = $"select * from AdminInfo where account='{@name}' and pwd='{@pwd}'";

            try
            {
                //防止sql注入
                if (sql.IndexOf("or") != -1) { throw new Exception("草泥马，想搞事？"); }
                conn.Open();
                cmd = new SqlCommand(sql, conn);
                object obj = cmd.ExecuteScalar();
                if (obj != null)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally { conn.Close(); }
        }

        /// <summary>
        /// 获得数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static DataTable GetDataTable(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                adapter = new SqlDataAdapter(sql, conn);
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取一行数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static DataRow GetDataRow(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                adapter = new SqlDataAdapter(sql, conn);
                adapter.Fill(ds);
                return ds.Tables[0].Rows[0];
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 执行非查询语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool NonQuery(string sql)
        {
            try
            {
                conn.Open();
                cmd= new SqlCommand(sql, conn);
                int row = cmd.ExecuteNonQuery();
                if(row > 0)
                {
                    conn.Close();
                    return true;
                }
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally { conn.Close(); }
            return false;
        }

        public static int GetFree(string bookid,string stuid)
        {
            try
            {
                string sql = $"select DATEDIFF(DD,starttime,GETDATE()) from RetrieveBook where bookid='{bookid}' and stuid ='{stuid}'";
                cmd = new SqlCommand(sql, conn);
                int day = Convert.ToInt32(cmd.ExecuteScalar());
                if (day > 90)
                {
                    return (day - 90) * 2;
                }
                else
                {
                    return 0;
                }
            }
            catch(Exception)
            {
                return 0;
            }
        }
    }
}
